package net.tngou.jtdb;

import java.io.IOException;
import java.io.StringReader;
import java.lang.reflect.InvocationTargetException;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.tngou.db.util.ResultSet;
import net.tngou.jtdb.sql.Connection;
import net.tngou.jtdb.sql.Statement;

public class TngouDBHelp {

	
	
	
	private static Connection con = null;
	private static TngouDBHelp tngouDBHelp=null;
	
	/**
	 * 
	* @Title: getConnection
	* @Description: 实现单链创建
	* @param @return    设定文件
	* @return TngouDBHelp    返回类型
	* @throws
	 */
	public static TngouDBHelp getConnection()  {
		if(tngouDBHelp!=null) return tngouDBHelp;
		return new TngouDBHelp();
		
	}
	
	private TngouDBHelp() {
		con=Connection.getConnection();
	}
	
	
	/**
	 * 
	* @Title: createTable
	* @Description:  创建表
	* @param @param tableName  表名
	* @return void    返回类型
	* @throws
	 */
	public void createTable(String tableName) {
		Statement stmt = con.createStatement();
		String sql ="create table "+tableName;
		try {
			 stmt.execute(sql);
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 
	* @Title: dropTable
	* @Description: TODO删除表
	* @param @param tableName    设定文件
	* @return void    返回类型
	* @throws
	 */
	public void dropTable(String tableName) {
		Statement stmt = con.createStatement();
		String sql ="drop table "+tableName;
		try {
			stmt.execute(sql);
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 
	* @Title: insert
	* @Description: 插入数据
	* @param @param tableName  插入数据表
	* @param @param fields    值
	* @return void    返回类型
	* @throws
	 */
	public void insert(String tableName ,Field ...fields) {
		
		Statement stmt = con.createStatement();
		String[] params =new String[fields.length];
		String fs="";
		String values="";
		for (int i = 0; i < fields.length; i++) {
			if(i!=0){fs+=",";values+=",";}
			fs+=fields[i].getName()+":"+fields[i].getType();
			values+="?";
			params[i]=fields[i].getValue();
		}
		
		String sql ="insert into "+tableName
				  +"("+fs+") "
				  + " values("+values+")";
	
//		System.out.println(sql);
		try {
			stmt.execute(sql, params);
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 
	* @Title: insert
	* @Description: 插入数据
	* @param @param tableName
	* @param @param fields    设定文件
	* @return void    返回类型
	* @throws
	 */
	public void insert(String tableName ,Fields fields) {
		List<Field> list = fields.getList();
		Field[] fs = (Field[]) list.toArray(new Field[list.size()] ); 
		insert( tableName ,fs);
		
	}
	
	
	/**
	 * 
	* @Title: select
	* @Description: 查询数据
	* @param @param tableName  表名
	* @param @param sortField  排序
	* @param @param page  当前页
	* @param @param size  大小
	* @param @param fields 查询条件
	* @param @return    设定文件
	* @return Page    返回类型
	* @throws
	 */
	public Page select(String tableName ,SortField sortField,int page,int size,Field ...fields) {
		
		 Page p = new Page();
		 if(page < 0 || size < 0){page=1;size=1;}
		 int from = (page - 1) * size;
		 size = (size > 0) ? size : Integer.MAX_VALUE;
		 Statement stmt = con.createStatement();
		 String sql="select * from " +tableName;
		 String[] params = null; 
		 if(fields.length>0)
		 {
			 params= new String[fields.length];
			 sql=sql+" where ";
			for (int i = 0; i < fields.length; i++) {
				if(i!=0)  sql=sql+" and ";
				sql=sql+fields[i].getName()+":"+fields[i].getType()+"=?";
				params[i]=fields[i].getValue();
			}
		 }
		 
		 if(sortField!=null)
			 sql=sql+ " order by "+sortField.getName()+":"+sortField.getType() +" "+ sortField.getOrder();
		 sql=sql+ " limit "+from+","+size;
		
		 ResultSet resultSet=null;
		 try {
				if(params==null)params= new String[0];
				
			     resultSet=stmt.execute(sql,params);
				BeanUtils.copyProperties(p, resultSet);
			} catch (IOException | InterruptedException | IllegalAccessException | InvocationTargetException e) {
				
				e.printStackTrace();
			}
		return p;
		
	}
	
	/**
	 * 
	* @Title: sql
	* @Description: 执行SQL语句，通用模式
	* @param @param sql  SQL语句
	* @param @param params  可变参数
	* @param @return    设定文件
	* @return ResultSet    返回类型
	* @throws
	 */
	public ResultSet execute(String sql,String ... params) {
		ResultSet resultSet = new ResultSet();
		Statement stmt = con.createStatement();
		try {
			resultSet= stmt.execute(sql, params);
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return resultSet;
	}
	
	
	/**
	 *  回收连接池
	* @Title: closeConnection
	* @Description: TODO(这里用一句话描述这个方法的作用)
	* @param     设定文件
	* @return void    返回类型
	* @throws
	 */
	public void closeConnection() {
		Connection.closeConnection(); // 回收链接
	}
	
	
	/**
	 * 
	* @Title: close
	* @Description: TODO关闭链接
	* @param     设定文件
	* @return void    返回类型
	* @throws
	 */
	public void close() {
		Connection.close();            // 关闭链接
	}
	
	
	
	

  
}
